﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace Sinacor.Deployment.Setup.DataAccess
{
    /// <summary>
    /// Classe que encapsula o acesso a base de dados oracle do sinacor
    /// </summary>
    public class OracleDataBase : IDisposable
    {
        private Dictionary<string, System.Data.OracleClient.OracleConnection> _connections;
        private const string SYSTEM_DATABASE_KEY = "SystemDatabase";
        private string _dataSource;
        private bool _isCompanyPasswordEncrypted;

        #region Construtor
        /// <summary>
        /// Criar um novo oracle database para acessar a base de empresa
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="dataSource"></param>
        /// <param name="isCompanyPasswordEncrypted">Indica se os passwords para acesso as bases de empresa estão criptografados</param>
        public OracleDataBase(string userName, string password, string dataSource, bool isCompanyPasswordEncrypted)
        {
            _isCompanyPasswordEncrypted = isCompanyPasswordEncrypted;
            _dataSource = dataSource;
            _connections = new Dictionary<string, OracleConnection>();

            //criar a conexao com a base de sistema
            OracleConnection systemDatabaseConnection = new OracleConnection(CreateConnectionString(userName, password, dataSource));
            _connections.Add(SYSTEM_DATABASE_KEY, systemDatabaseConnection);
        }

        /// <summary>
        /// Criar um novo oracle database para acessar a base de empresa
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="dataSource"></param>
        public OracleDataBase(string userName, string password, string dataSource)
            : this(userName, password, dataSource, false)
        {
        }

        /// <summary>
        /// Recuperar a conexao com a base de sistema
        /// </summary>
        /// <returns></returns>
        protected OracleConnection GetSystemConnection()
        {
            if (_connections.ContainsKey(SYSTEM_DATABASE_KEY))
                return _connections[SYSTEM_DATABASE_KEY];
            else
                return null;
        }

        /// <summary>
        /// Recuperar/Criar e armazenar a conexões com a base de empresa utilizando a conexao de sistema para acessar a tablema TGEEMPR
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        protected OracleConnection GetCompanyConnection(int companyId, bool isPasswordEncrypted)
        {
            OracleConnection companyConnection = null;
            if (!this._connections.ContainsKey(companyId.ToString()))
            {
                string query = "SELECT CD_EMPRESA,NM_EMPRESA,CD_USUA_ORAC,CD_SENHA_ORAC FROM TGEEMPR WHERE CD_EMPRESA =:CD_EMPRESA";
                OracleConnection systemDatabaseConnection = _connections[SYSTEM_DATABASE_KEY];
                if (systemDatabaseConnection != null)
                {
                    try
                    {
                        systemDatabaseConnection.Open();
                        using (OracleCommand command = new OracleCommand(query, systemDatabaseConnection))
                        {
                            command.Parameters.Add(new OracleParameter("CD_EMPRESA", companyId));
                            IDataReader reader = command.ExecuteReader();
                            while (reader.Read())
                            {
                                string password;
                                //DataEncryptDecrypt
                                if (isPasswordEncrypted)
                                    password = DataEncryptDecrypt(reader.GetString(3));
                                else
                                    password = reader.GetString(3);

                                companyConnection = new OracleConnection(CreateConnectionString(reader.GetString(2), password, this._dataSource));
                                this._connections.Add(reader.GetInt32(0).ToString(), companyConnection);
                            }
                        }
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        systemDatabaseConnection.Close();
                    }
                }
            }
            else
            {
                companyConnection = this._connections[companyId.ToString()];
            }

            return companyConnection;
        }

        #endregion


        #region Execução de scripts
        /// <summary>
        /// Executa um script na base de empresa, de acordo com a empresa informada
        /// </summary>
        /// <param name="companyId">Id da empresa</param>
        /// <param name="query">Query a ser executada</param>
        public bool ExecuteNonQuery(int companyId, string query)
        {
            OracleConnection connection = this.GetCompanyConnection(companyId, this._isCompanyPasswordEncrypted);
            return ExecuteNonQuery(query, connection);
        }

        /// <summary>
        /// Executa um script na base de sistema
        /// </summary>
        /// <param name="query">Query a ser executada</param>
        public bool ExecuteNonQuery(string query)
        {
            OracleConnection connection = this.GetSystemConnection();
            return ExecuteNonQuery(query, connection);
        }

        /// <summary>
        /// Executa um select na base de dados de sistema
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string query, List<DataBaseParameter> parameters)
        {
            OracleConnection connection = this.GetSystemConnection();
            return this.ExecuteReader(query, connection, parameters);
        }

        /// <summary>
        ///  Executa um select na base de dados de empresa, de acordo com o companyId informado
        /// </summary>
        /// <param name="companyId">Id da empresa</param>
        /// <param name="query">Query a ser executada</param>
        /// <param name="parameters">Parâmetros de execução</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(int companyId, string query, List<DataBaseParameter> parameters)
        {
            OracleConnection connection = this.GetCompanyConnection(companyId, this._isCompanyPasswordEncrypted);
            return this.ExecuteReader(query, connection, parameters);
        }

        /// <summary>
        /// Executa um select na base de dados de sistema
        /// </summary>
        /// <param name="query">Query a ser executada</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, null);
        }

        /// <summary>
        /// Executa um select na base de dados de empresa, de acordo com o companyId informado
        /// </summary>
        /// <param name="companyId">Id da empresa</param>
        /// <param name="query">Query a ser executada</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(int companyId, string query)
        {
            return ExecuteReader(companyId, query, null);
        }

        /// <summary>
        /// Executar uma query
        /// </summary>
        /// <param name="query">Query a ser executada</param>
        /// <param name="connection">Conexão utilizada para execução da query</param>
        /// <returns></returns>
        private bool ExecuteNonQuery(string query, OracleConnection connection)
        {
            try
            {
                connection.Open();
                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    return command.ExecuteNonQuery() > 0;
                }
            }
            catch
            {
                throw; //apenas subir o erro para a camada acima tratar
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Executar uma query
        /// </summary>
        /// <param name="query">Query a ser executada</param>
        /// <param name="connection">Conexão utilizada para execução da query</param>
        /// <param name="parameters">Parâmetros de execução da query</param>
        /// <returns></returns>
        private IDataReader ExecuteReader(string query, OracleConnection connection, List<DataBaseParameter> parameters)
        {
            if(connection.State != ConnectionState.Open)
                connection.Open();
            
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                if (parameters != null)
                    foreach (DataBaseParameter parm in parameters)
                        command.Parameters.Add(new OracleParameter(parm.Name, parm.Value));

                return command.ExecuteReader();
            }


        }
        #endregion

        #region IDisposable Members
        public void Dispose()
        {
            foreach (OracleConnection connection in this._connections.Values)
            {
                connection.Close();
                connection.Dispose();
            }
            this._connections.Clear();
        }
        #endregion

        /// <summary>
        /// Criar uma string de conexao
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <param name="datasource"></param>
        /// <returns></returns>
        protected string CreateConnectionString(string user, string password, string datasource)
        {
            if (user == null)
                throw new ArgumentNullException("user");
            if (password == null)
                throw new ArgumentNullException("password");
            if (datasource == null)
                throw new ArgumentNullException("datasource");

            //TODO: recuperar valores padrao de arquivo de configuracao
            string maxPoolSize = "5";
            string minPoolSize = "1";
            string pooling = "true";

            string newConnection = "Pooling=" + pooling +
                                   ";Min Pool Size=" + minPoolSize +
                                   ";Max Pool Size=" + maxPoolSize +
                                   ";Password=" + password +
                                   ";User ID=" + user +
                                   ";Data Source=" + datasource +
                                   ";Persist Security Info=True";
            return newConnection;
        }

        /// <summary>
        /// Cifrar/Decifrar um valor utilizando o algoritimo atualmente utilizado 
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private string DataEncryptDecrypt(string value)
        {
            Int32 Contador;
            Char Caracter;
            Int32 Novo;
            string textoCriptografado;

            textoCriptografado = string.Empty;

            for (Contador = 0; Contador < value.Length; Contador++)
            {
                Caracter = value.Substring(Contador, 1).ToCharArray()[0];
                Novo = 90 - (Caracter - 48) + Contador + 1;
                Caracter = Char.ConvertFromUtf32(Novo).ToCharArray()[0];

                textoCriptografado += Caracter.ToString();
            }

            return textoCriptografado;
        }
    }
}
